﻿// <author>Diogo Silva 11238</author>
// <author>Raúl Gonçalo Palma 11392</author>
// <date>05/01/2015</date>

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSharpProject
{
    /// <summary>
    ///  Classe que efetua operacões relacionadas com a base de dados
    /// </summary>
    class DbHandler
    {
        public const int FIELD_IPC = 0;
        public const int FIELD_VARANUAL = 1;
        public const int FIELD_REMUNMINMENSAL = 2;
        public const int FIELD_REMUNMAXMENSAL = 3;
        public const int FIELD_PIB = 4;
        public const int FIELD_RENDNACIONALBRUTO = 5;
        public const int FIELD_RENDDISPLBRUTOANUAL = 6;
        public const int FIELD_REMUNPERCAPITAANUAL = 7;
        public const int FIELD_EVOREMUNMIN = 8;
        public const int FIELD_EVOREMUNMAX = 9;

        private static string DBNAME = @"Data Source = evoSalarios.db";
        private DataContext context;

        /// <summary>
        ///  Construtor da classe DbHandler.
        /// </summary>
        public DbHandler()
        {
            SQLiteConnection connection = new SQLiteConnection(DBNAME);
            this.context = new DataContext(connection); // permite manipular a base de dados querys, commit's etc
        }

        /// <summary>
        ///  Insere os dados numa tabela
        ///  param name="lines" - Contém as linhas do ficheiro XLS.
        /// </summary>
        public void InsertData(List<XlsLine> lines)
        {
            var evoSalarios = this.context.GetTable<XlsLine>();
            evoSalarios.InsertAllOnSubmit(lines);
            this.context.SubmitChanges();
        }


        /// <summary>
        ///  Faz uma  consulta à base de dados
        ///  param name="initYear" - Ano inical
        ///  param name="finalYear" - Ano final
        ///  param name="field" - Campo da tabela
        /// </summary>
        public Tuple<List<int>, List<double>> query(int initYear, int finalYear, int field)
        {
            var evoSalarios = this.context.GetTable<XlsLine>();
            var filtered = evoSalarios.Where(value => value.ano >= initYear && value.ano <= finalYear).ToList();
            List<int> years = new List<int>();
            List<double> values = new List<double>();
            
            switch (field)
            {
                case FIELD_IPC:
                    { 
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.ipcGlobal);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_VARANUAL:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.variacaoAnual);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_REMUNMINMENSAL:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.remunMinimaMensal);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_REMUNMAXMENSAL:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.remunMaximaMensal);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_PIB:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.pib);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_RENDNACIONALBRUTO:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.rendiNacionalBruto);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_RENDDISPLBRUTOANUAL:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.renDisponivelBrutoAnual);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_REMUNPERCAPITAANUAL:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            values.Add(value.remunPerCapitaAnual);
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_EVOREMUNMIN:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            if(value.ipcGlobal == 0.0)
                            {
                                values.Add(0.0);
                            }
                            else
                            {
                                values.Add(value.remunMinimaMensal / value.ipcGlobal);
                            }
                            years.Add(value.ano);
                        }
                        break;
                    }
                case FIELD_EVOREMUNMAX:
                    {
                        foreach (XlsLine value in filtered)
                        {
                            if (value.ipcGlobal == 0.0)
                            {
                                values.Add(0.0);
                            }
                            else
                            {
                                values.Add(value.remunMaximaMensal / value.ipcGlobal);
                            }
                            years.Add(value.ano);
                        }
                        break;
                    }
            }
            return Tuple.Create(years, values);
        }

        /// <summary>
        ///  Limpa a tabela
        /// </summary>
        public void clearTable()
        {
            var evoSalarios = this.context.GetTable<XlsLine>();
            evoSalarios.DeleteAllOnSubmit(evoSalarios.ToList());
            this.context.SubmitChanges();
        }
    }
}
